<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

/*********************************************************************************
 * SugarCRM Community Edition is a customer relationship management program developed by
 * SugarCRM, Inc. Copyright (C) 2004-2013 SugarCRM Inc.
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU Affero General Public License version 3 as published by the
 * Free Software Foundation with the addition of the following permission added
 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU Affero General Public License along with
 * this program; if not, see http://www.gnu.org/licenses or write to the Free
 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 * 
 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
 * 
 * The interactive user interfaces in modified source and object code versions
 * of this program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU Affero General Public License version 3.
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
 * these Appropriate Legal Notices must retain the display of the "Powered by
 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
 * technical reasons, the Appropriate Legal Notices must display the words
 * "Powered by SugarCRM".
 ********************************************************************************/

/*********************************************************************************

 * Description: Class to handle processing an import file
 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
 * All Rights Reserved.
 ********************************************************************************/

require_once('modules/Import/CsvAutoDetect.php');
require_once('modules/Import/sources/ImportDataSource.php');
require_once('include/phpExcel/PHPExcel.php'); 

class ImportFile extends ImportDataSource
{
    /**
     * Stores whether or not we are deleting the import file in the destructor
     */
    private $_deleteFile;
	
	private $_fileType;

    /**
     * File pointer returned from fopen() call
     */
    private $_fp = FALSE;

    /**
     * True if the csv file has a header row.
     */
    private $_hasHeader = FALSE;

    /**
     * True if the csv file has a header row.
     */
    private $_detector = null;

    /**
     * CSV date format
     */
    private $_date_format = false;

    /**
     * CSV time format
     */
    private $_time_format = false;

    /**
     * The import file map that this import file inherits properties from.
     */
    private $_importFile = null;

    /**
     * Delimiter string we are using (i.e. , or ;)
     */
    private $_delimiter;

    /**
     * Enclosure string we are using (i.e. ' or ")
     */
    private $_enclosure;
    
    /**
     * File encoding, used to translate the data into UTF-8 for display and import
     */
    private $_encoding;


    /**
     * Constructor
     *
     * @param string $filename
     * @param string $delimiter
     * @param string $enclosure
     * @param bool   $deleteFile
     */
    public function __construct( $filename, $delimiter  = ',', $enclosure  = '',$deleteFile = true, $checkUploadPath = TRUE )
    {
        if ( !is_file($filename) || !is_readable($filename) ) {
            return false;
        }

        if ( $checkUploadPath && UploadStream::path($filename) == null )
        {
            $GLOBALS['log']->fatal("ImportFile detected attempt to access to the following file not within the sugar upload dir: $filename");
            return null;
        }

        // turn on auto-detection of line endings to fix bug #10770
        ini_set('auto_detect_line_endings', '1');
		$isExcel=true;
        $this->_fp = sugar_fopen($filename,'r');
		$fopen=fopen("include/tmp.xlsx",'w');
		while(!feof($this->_fp)) {
			$fcontent = fread($this->_fp,filesize($filename));
			fwrite($fopen,$fcontent);
		}
		fclose($fopen);
		$PHPExcel = new PHPExcel(); 
		
		$PHPReader = new PHPExcel_Reader_Excel2007(); 
		$filename = "include/tmp.xlsx";
		
		if(!$PHPReader->canRead($filename)){ 
			$PHPReader = new PHPExcel_Reader_Excel5(); 
			if(!$PHPReader->canRead($filename)){ 
				$isExcel=false; 
			//	return ; 
			} 
			
		} 
		if($isExcel)
				$this->_fileType = 'xlsx';
		if(!$isExcel) {
			$this->_fpexcel = sugar_fopen($filename,'r');
			$fopen=fopen("include/tmp.xml",'w');
			while(!feof($this->_fpexcel)) {
				$fcontent = fread($this->_fpexcel,filesize($filename));
				fwrite($fopen,$fcontent);
			}
			fclose($fopen);
			$this->_fileType = 'xml';
		}
        $this->_sourcename   = $filename;
        $this->_deleteFile = $deleteFile;
        $this->_delimiter  = ( empty($delimiter) ? ',' : $delimiter );
        if ($this->_delimiter == '\t') {
            $this->_delimiter = "\t";
        }
        $this->_enclosure  = ( empty($enclosure) ? '' : trim($enclosure) );

        // Autodetect does setFpAfterBOM()
        $this->_encoding = $this->autoDetectCharacterSet();
    }

    /**
     * Remove the BOM (Byte Order Mark) from the beginning of the import row if it exists
     * @return void
     */
    private function setFpAfterBOM()
    {
        if($this->_fp === FALSE)
            return;

        rewind($this->_fp);
        $bomCheck = fread($this->_fp, 3);
		
        if($bomCheck != pack("CCC",0xef,0xbb,0xbf)) {
            rewind($this->_fp);
        }
    }
    /**
     * Destructor
     *
     * Deletes $_importFile if $_deleteFile is true
     */
    public function __destruct()
    {
        if ( $this->_deleteFile && $this->fileExists() ) {
            fclose($this->_fp);
            //Make sure the file exists before unlinking
            if(file_exists($this->_sourcename)) {
               unlink($this->_sourcename);
            }
        }

        ini_restore('auto_detect_line_endings');
    }

    /**
	 * This is needed to prevent unserialize vulnerability
     */
    public function __wakeup()
    {
        // clean all properties
        foreach(get_object_vars($this) as $k => $v) {
            $this->$k = null;
        }
        throw new Exception("Not a serializable object");
    }

    /**
     * Returns true if the filename given exists and is readable
     *
     * @return bool
     */
    public function fileExists()
    {
    	return !$this->_fp ? false : true;
    }

	public function getXMLRows(){
		$dom = new DOMDocument();
		$dom->load("include/tmp.xml");
		$elements = $dom->getElementsByTagName("gridSpan");
		foreach($elements as $element)
		{//var_dump("@");
			//var_dump($element);}
		$i=0;
		$spilt_string=null;
		foreach($elements as $element) {
			$spilt_string[$i]=$element->nodeValue;
			$i++;
		}
		//var_dump($spilt_string);
		$string=$dom->textContent;
		
		$names=array("企业名称" ,  "企业注册地址" , "组织机构代码（9位）" , "注册资金" , 
		"注册年月" , "法定代表人" , "移动电话" , "办公电话" ,  "联系人" , 
		"移动电话" , "办公电话" , "登记注册类型" , "股东及持股比例" , "是否经认定为省级科技型中小企业" , 
		"是否经认定为高新技术企业" , "主要产品所属技术领域","企业简介" ,"主营业务或主要产品","企业的资质认证（如行业资质认证）或奖励称号" , "主营产品技术水平"  ,"主要产品收入占企业销售收入的百分比" ,  
		
		
		"企业总人数" ,  "管理人员总数" ,  "硕士及以上人数" ,  "科研人员总数" , "本科学历人数" ,  "营销人员总数" ,  "大专学历人数" ,  "其他人员总数" ,  "其他学历人数" ,


		"主要产品" , "销售单价（元）" , "生产成本（元）" , "毛利（%）" , "销售额（万元）" , "占总销售额的比例（%）" , 

		"供应商名称" , "采购品种" , "采购量" , "采购金额" , "占总采购量的比重" , 
		
		"销售客户" , "销售产品" , "销售量" , "销售金额" ,"占总销售额的比重" , 
		
		"2011年" , "2012年" , "2013年" , "企业总资产（万元）" , "企业负债总额（万元）" , "主营业务收入（万元）" , "利润总额（万元）" ,  "资产负债率（%）" ,  "速动比率" , "流动比率" , "总资产报酬率" , "创投机构融资额(万元)" , "金融机构融资额(万元)" , "政府拨款额度(万元)" ,  "其中：专项奖励（万元）" ,  "是否有独立的技术研发机构" ,"研发机构是否获得称号" , "如是，请填写称号：" , "迄今拥有专利数量(项)" , "其中：发明专利数量(项)" , "开发新产品数量（项）" , "完成研发项目数量(项)" ,  "研发费用（万元）" , "是否有高新技术产品" , "如是，请填写称号：" , "研发费用占销售收入的比重" , "企业核心技术优势" , "过去一年的融资总量（万元）" , "今年是否有融资需求" , "如是，请认真填写以下内容" , "计划融资额度","股权融资（万元）" , "资金用途" ,"债权融资（万元）" ,  "拟融资项目" ,  "产品介绍(可附加页)" ,  "姓名" , "性别" ,"出生年月" , "职务" ,  "民族" , "籍贯" , "最高学历"  , "电话" ,"邮箱" ,  "何年/何校/何专业"  , "身份证号码" , "家庭住址" , "工作简历" , "起止时间" , "工作单位" , "所任职务" , "获得荣誉或奖励" , "获得时间" , "荣誉称号" , "荣誉级别注：荣誉级别是指县/区级、市级、省级或国家级等");
		$index=0;
		$value=null;
		//$arrLen=count($key);
		//$string='Test document for Office 2007 XLSX, generated using PHP classes.';
		while($index<30) {
			$pos=strpos($string,$names[$index]);
			$nextPos=strpos($string,$names[$index+1]);
			$keylen=strlen($names[$index]);
			$value[$index]=trim(substr($string,$pos+$keylen,$nextPos-$pos-$keylen));
			if(strpos($value[$index],'企业人员结构'))
				$value[$index]=trim(substr($value[$index],0,strpos($value[$index],'企业人员结构')));
			if(strpos($value[$index],'学历结构'))
				$value[$index]=trim(substr($value[$index],0,strpos($value[$index],'学历结构')));
			if(strpos($value[$index],'二、经营情况'))
				$value[$index]=trim(substr($value[$index],0,strpos($value[$index],'二、经营情况')));
			$string=substr($string,$pos+$keylen);
			$index++;
		}
		$ms_1=trim($names[$index]).trim($names[++$index]).trim($names[++$index]).trim($names[++$index]).trim($names[++$index]).trim($names[++$index]);
		$ms_2=trim($names[++$index]).trim($names[++$index]).trim($names[++$index]).trim($names[++$index]).trim($names[++$index]);
		$ms_3=trim($names[++$index]).trim($names[++$index]).trim($names[++$index]).trim($names[++$index]).trim($names[++$index]);
		str_replace(' ','', $ms_1);
		str_replace(' ','', $ms_2);
		str_replace(' ','', $ms_3);
		
		/*foreach($spilt_string $key=>$value) {
			str_replace(' ','', $value);
			if(trim($value)=='二、经营情况') 
				$ms_start=$key+1;
			$ms_doc_1=null;
			if($key>=$ms_start && strlen($ms_doc_1)<=strlen($ms_1))
				$ms_doc_1=$ms_doc_1.$value;
			if($ms_doc_1==$ms_1)
				$ms_end=$key+1;
			if($key>=$ms_end && $value<)
				
		}*/
		
		return $value;
	}
	
    /**
     * Gets the next row from $_importFile
     *
     * @return array current row of file
     */
	 
	public function getRowsForSample()
    {
        //$this->_currentRow = FALSE;
		$this->_currentRow = array();
		
        if (!$this->fileExists())
        {
            return false;
        }
        
		//var_dump($row);
		$PHPExcel = new PHPExcel(); 
		
		$PHPReader = new PHPExcel_Reader_Excel2007(); 
		$filename = "include/tmp.xlsx";
		
		if(!$PHPReader->canRead($filename)){ 
			$PHPReader = new PHPExcel_Reader_Excel5(); 
			if(!$PHPReader->canRead($filename)){ 
				echo 'no Excel'; 
			//	return ; 
			} 
		} 

		$PHPExcel = $PHPReader->load($filename); 
		$currentSheet = $PHPExcel->getSheet(0); 
		//$this->_currentRow
		$rowNum = $currentSheet->getHighestRow(); 
		$colNum = ord($currentSheet->getHighestColumn())-65;
		//var_dump($rowNum);
		//var_dump($colNum);
		$row=2;
		$index=0;
		global $locale;
		while($row<($rowNum+1)) {
			
			$col=0;
			$nullIndex=0;
			while($col<$colNum) {
				if($currentSheet->getCellByColumnAndRow($col,$row)->getValue()==NULL) 
					$nullIndex++;
				$col++;
			}
			if($nullIndex<($colNum-1)) {
				$col=0;
				$this->_currentRow[$index] = array();
				while($col<$colNum+1) {
					if (!empty($this->_encoding))
						$this->_currentRow[$index][$col] = ''.$currentSheet->getCellByColumnAndRow($col,$row)->getValue();//, $this->_encoding));
						//preg_replace("[\r\n|\n|\r]", PHP_EOL, $locale->translateCharset(
					else
						$this->_currentRow[$index][$col] = ''.$currentSheet->getCellByColumnAndRow($col,$row)->getValue();
						//preg_replace("[\r\n|\n|\r]", PHP_EOL, 
					$col++;
					
				}
				$index++;
			}
			$row++;
			
		}
       /* global $locale;
        foreach ($this->_currentRow as $key => $value)
        {
		
			//var_dump($value);
            // If encoding is set, convert all values from it
            if (!empty($this->_encoding))
            {
                // Convert all values to UTF-8 for display and import purposes
                $this->_currentRow[$key] = $locale->translateCharset($value, $this->_encoding);
            }
            
            // Convert all line endings to the same style as PHP_EOL
            // Use preg_replace instead of str_replace as str_replace may cause extra lines on Windows
            $this->_currentRow[$key] = preg_replace("[\r\n|\n|\r]", PHP_EOL, $this->_currentRow[$key]);
        }*/
		
		//var_dump($this->_currentRow);
        
        $this->_rowsCount++;
		//var_dump($this->_currentRow);
        return $this->_currentRow;
    } 
	
	public function getSourceName()
	{
		$sn = '';
		if(stripos($this->_sourcename,'badinfo'))
			$sn='badinfo';
		return $sn;
	}
	
	public function getFileType()
	{
		return $this->_fileType;
	}
	
    public function getNextRow()
    {
        //$this->_currentRow = FALSE;
		$this->_currentRow = array();
		
        if (!$this->fileExists())
        {
            return false;
        }
        
		/*if (empty($this->_enclosure))
        {
            $row = explode($this->_delimiter, rtrim(fgets($this->_fp, 8192), "\r\n"));
            if ($row !== false && !(count($row) == 1 && trim($row[0]) == ''))
            {
                $this->_currentRow = $row;
            }
            else
            {
                return false;
            }
        }
        else
        {
            $row = fgetcsv($this->_fp, 8192, $this->_delimiter, $this->_enclosure);
            if ($row !== false && $row != array(null))
            {
                $this->_currentRow = $row;
            }
            else
            {
                return false;
            }
        }*/
		
		//var_dump($row);
		$PHPExcel = new PHPExcel(); 
		
		$PHPReader = new PHPExcel_Reader_Excel2007(); 
		$filename = "include/tmp.xlsx";
		
		if(!$PHPReader->canRead($filename)){ 
			$PHPReader = new PHPExcel_Reader_Excel5(); 
			if(!$PHPReader->canRead($filename)){ 
				echo 'no Excel'; 
			//	return ; 
			} 
		} 

		$PHPExcel = $PHPReader->load($filename); 
		$currentSheet = $PHPExcel->getSheet(0); 
		//$this->_currentRow
		$rowNum = $currentSheet->getHighestRow(); 
		$colNum = ord($currentSheet->getHighestColumn())-65;
		//var_dump($rowNum);
		//var_dump($colNum);
		$row=2;
		
		while($row<($rowNum+1)) {
			
			$col=0;
			$nullIndex=0;
			while($col<$colNum) {
				if($currentSheet->getCellByColumnAndRow($col,$row)->getValue()==NULL) 
					$nullIndex++;
				$col++;
			}
			if($nullIndex<6) {
				$col=0;
				while($col<$colNum) {
					$this->_currentRow[$col] = ''.$currentSheet->getCellByColumnAndRow($col,$row)->getValue();
					$col++;
				}
			}
			$row++;
			
		}
        global $locale;
        foreach ($this->_currentRow as $key => $value)
        {
		
			//var_dump($value);
            // If encoding is set, convert all values from it
            if (!empty($this->_encoding))
            {
                // Convert all values to UTF-8 for display and import purposes
                $this->_currentRow[$key] = $locale->translateCharset($value, $this->_encoding);
            }
            
            // Convert all line endings to the same style as PHP_EOL
            // Use preg_replace instead of str_replace as str_replace may cause extra lines on Windows
            $this->_currentRow[$key] = preg_replace("[\r\n|\n|\r]", PHP_EOL, $this->_currentRow[$key]);
        }
        
        $this->_rowsCount++;
        return $this->_currentRow;
    }

    /**
     * Returns the number of fields in the current row
     *
     * @return int count of fiels in the current row
     */
    public function getFieldCount()
    {
        return count($this->_currentRow);
    }

    /**
     * Determine the number of lines in this file.
     *
     * @return int
     */
    public function getNumberOfLinesInfile()
    {
        $lineCount = 0;

        if ($this->_fp )
        {
            rewind($this->_fp);
            while( !feof($this->_fp) )
            {
                if( fgets($this->_fp) !== FALSE)
                    $lineCount++;
            }
            //Reset the fp to after the bom if applicable.
            $this->setFpAfterBOM();
        }

        return $lineCount;
    }

    //TODO: Add auto detection for field delim and qualifier properteis.
    public function autoDetectCSVProperties()
    {
        // defaults
        $this->_delimiter  = ",";
        $this->_enclosure  = '"';

        $this->_detector = new CsvAutoDetect($this->_sourcename);

        $delimiter = $enclosure = false;

        $ret = $this->_detector->getCsvSettings($delimiter, $enclosure);
        if ($ret)
        {
            $this->_delimiter = $delimiter;
            $this->_enclosure = $enclosure;
            return TRUE;
        }
        else
        {
            return FALSE;
        }
    }

    public function getFieldDelimeter()
    {
        return $this->_delimiter;
    }

    public function getFieldEnclosure()
    {
        return $this->_enclosure;
    }

    public function autoDetectCharacterSet()
    {
        // If encoding is already detected, just return it
        if (!empty($this->_encoding))
        {
            return $this->_encoding;
        }
        
        // Move file pointer to start
        $this->setFpAfterBOM();
        
        global $locale;
        $user_charset = $locale->getExportCharset();
        $system_charset = $locale->default_export_charset;
        $other_charsets = 'UTF-8, UTF-7, ASCII, CP1252, EUC-JP, SJIS, eucJP-win, SJIS-win, JIS, ISO-2022-JP';
        $detectable_charsets = "UTF-8, {$user_charset}, {$system_charset}, {$other_charsets}";

        // Bug 26824 - mb_detect_encoding() thinks CP1252 is IS0-8859-1, so use that instead in the encoding list passed to the function
        $detectable_charsets = str_replace('CP1252', 'ISO-8859-1', $detectable_charsets);
        
        // If we are able to detect encoding
        if (function_exists('mb_detect_encoding'))
        {
            // Retrieve a sample of data set
            $text = '';
            
            // Read 10 lines from the file and put them all together in a variable
            $i = 0;
            while ($i < 10 && $temp = fgets($this->_fp, 8192))
            {
                $text .= $temp;
                $i++;
            }
            
            // If we picked any text, try to detect charset
            if (strlen($text) > 0)
            {
                $charset_for_import = mb_detect_encoding($text, $detectable_charsets);
            }
        }
        
        // If we couldn't detect the charset, set it to default export/import charset 
        if (empty($charset_for_import))
        {
            $charset_for_import = $locale->getExportCharset(); 
        }
        
        // Reset the fp to after the bom if applicable.
        $this->setFpAfterBOM();
        
        return $charset_for_import;

    }

    public function getDateFormat()
    {
        if ($this->_detector) {
            $this->_date_format = $this->_detector->getDateFormat();
        }

        return $this->_date_format;
    }

    public function getTimeFormat()
    {
        if ($this->_detector) {
            $this->_time_format = $this->_detector->getTimeFormat();
        }

        return $this->_time_format;
    }

    public function setHeaderRow($hasHeader)
    {
        $this->_hasHeader = $hasHeader;
    }

    public function hasHeaderRow($autoDetect = TRUE)
    {
        if($autoDetect)
        {
            if (!isset($_REQUEST['import_module']))
                return FALSE;

            $module = $_REQUEST['import_module'];

            $ret = FALSE;
            $heading = FALSE;

            if ($this->_detector)
                $ret = $this->_detector->hasHeader($heading, $module, $this->_encoding);

            if ($ret)
                $this->_hasHeader = $heading;
        }
        return $this->_hasHeader;
    }

    public function setImportFileMap($map)
    {
        $this->_importFile = $map;
        $importMapProperties = array('_delimiter' => 'delimiter','_enclosure' => 'enclosure', '_hasHeader' => 'has_header');
        //Inject properties from the import map
        foreach($importMapProperties as $k => $v)
        {
            $this->$k = $map->$v;
        }
    }

    //Begin Implementation for SPL's Iterator interface
    public function key()
    {
        return $this->_rowsCount;
    }

    public function current()
    {
        return $this->_currentRow;
    }

    public function next()
    {
        $this->getNextRow();
    }

    public function valid()
    {
        return $this->_currentRow !== FALSE;
    }

    public function rewind()
    {
        $this->setFpAfterBOM();
        //Load our first row
        $this->getNextRow();
    }

    public function getTotalRecordCount()
    {
        $totalCount = $this->getNumberOfLinesInfile();
        if($this->hasHeaderRow(FALSE) && $totalCount > 0)
        {
            $totalCount--;
        }
        return $totalCount;
    }

    public function loadDataSet($totalItems = 0)
    {
        $currentLine = 0;
        $this->_dataSet = array();
        $this->rewind();
        //If there's a header don't include it.
        if( $this->hasHeaderRow(FALSE) )
            $this->next();

        while( $this->valid() &&  $totalItems > count($this->_dataSet) )
        {
            if($currentLine >= $this->_offset)
            {
                $this->_dataSet[] = $this->_currentRow;
            }
            $this->next();
            $currentLine++;
        }

        return $this;
    }

    public function getHeaderColumns()
    {
        $this->rewind();
        if($this->hasHeaderRow(FALSE))
            return $this->_currentRow;
        else
            return FALSE;
    }

}

        // explode on delimiter instead if enclosure is an empty string
        /*if (empty($this->_enclosure))
        {
            $row = explode($this->_delimiter, rtrim(fgets($this->_fp, 8192), "\r\n"));
            if ($row !== false && !(count($row) == 1 && trim($row[0]) == ''))
            {
                $this->_currentRow = $row;
            }
            else
            {
                return false;
            }
        }
        else
        {
            $row = fgetcsv($this->_fp, 8192, $this->_delimiter, $this->_enclosure);
            if ($row !== false && $row != array(null))
            {
                $this->_currentRow = $row;
            }
            else
            {
                return false;
            }
        }*/
